import pandas as pd

# 定义LSR计算函数
def calculate_lsr(controllers, short_debt, long_debt):
    state_owned_types = ['1100', '2000', '2100', '2120']
    if str(controllers[0]) in state_owned_types:
        lsr = short_debt + 0.5 * long_debt
    else:
        lsr = short_debt + 0.4 * long_debt
    return lsr

# 加载Excel文件
file_path = r'C:\Users\程\PycharmProjects\pythonProject3\a2.xlsx'
data = pd.read_excel(file_path)

# 将'实际控制人性质'列转换为字符串
data['实际控制人性质'] = data['实际控制人性质'].astype(str)

# 应用LSR函数计算每个企业的LSR值
data['LSR'] = data.apply(lambda row: calculate_lsr(row['实际控制人性质'], row['短期负债'], row['长期负债合计']), axis=1)

# 将更新后的DataFrame保存到原始Excel文件
output_file_path = file_path.replace('.xlsx', '_updated.xlsx')
data.to_excel(output_file_path, index=False)
